-- 创建数据库
CREATE
DATABASE microservice_db;

-- 切换到创建的数据库
\c
microservice_db;

-- 启用pgcrypto扩展（用于数据库原生加密）
CREATE
EXTENSION IF NOT EXISTS pgcrypto;

-- 创建商品表
CREATE TABLE t_product
(
    id           BIGSERIAL PRIMARY KEY,
    product_name VARCHAR(100)   NOT NULL,
    description  VARCHAR(255),
    price        DECIMAL(10, 2) NOT NULL,
    stock        INT            NOT NULL DEFAULT 0,
    status       INT            NOT NULL DEFAULT 1, -- 1-正常, 0-下架
    create_time  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE t_order
(
    id                    BIGSERIAL PRIMARY KEY,
    user_id               BIGINT         NOT NULL,
    order_no              VARCHAR(50)    NOT NULL UNIQUE,
    total_amount          DECIMAL(10, 2) NOT NULL,
    status                INT            NOT NULL DEFAULT 0, -- 0-待支付, 1-已支付, 2-已取消
    -- 收货人信息（使用PostgreSQL pgcrypto原生加密）
    receiver_name         TEXT,                              -- 收货人姓名（pgcrypto加密）
    receiver_phone        TEXT,                              -- 收货人手机号（pgcrypto加密）
    receiver_phone_prefix VARCHAR(3),                        -- 手机号前3位（用于前缀查询）
    receiver_phone_suffix VARCHAR(4),                        -- 手机号后4位（用于后缀查询）
    receiver_name_hash    VARCHAR(64),                       -- 姓名确定性加密哈希（用于索引查询）
    receiver_phone_hash   VARCHAR(64),                       -- 手机号确定性加密哈希（用于索引查询）
    receiver_province     VARCHAR(50)    NOT NULL,           -- 收货省份
    receiver_city         VARCHAR(50)    NOT NULL,           -- 收货城市
    receiver_district     VARCHAR(50)    NOT NULL,           -- 收货区/县
    receiver_address      VARCHAR(255)   NOT NULL,           -- 详细收货地址
    create_time           TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time           TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 为加密哈希字段创建索引（支持按姓名和手机号查询）
CREATE INDEX idx_order_receiver_name_hash ON t_order (receiver_name_hash);
CREATE INDEX idx_order_receiver_phone_hash ON t_order (receiver_phone_hash);
CREATE INDEX idx_order_receiver_phone_prefix ON t_order (receiver_phone_prefix);
CREATE INDEX idx_order_receiver_phone_suffix ON t_order (receiver_phone_suffix);

-- 创建订单项表
CREATE TABLE t_order_item
(
    id            BIGSERIAL PRIMARY KEY,
    order_id      BIGINT         NOT NULL,
    product_id    BIGINT         NOT NULL,
    product_name  VARCHAR(100)   NOT NULL,
    product_price DECIMAL(10, 2) NOT NULL,
    quantity      INT            NOT NULL,
    total_price   DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES t_order (id)
);

-- 初始化商品数据
INSERT INTO t_product (product_name, description, price, stock, status)
VALUES ('iPhone 13', 'Apple iPhone 13 128GB', 5999.00, 100, 1),
       ('华为Mate 40', '华为Mate 40 Pro 256GB', 6499.00, 80, 1),
       ('小米12', '小米12 12GB+256GB', 4299.00, 150, 1);

-- 创建更新时间触发器函数
CREATE
OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time
= CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

-- 为表添加更新时间触发器
CREATE TRIGGER update_product_modtime
    BEFORE UPDATE
    ON t_product
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_order_modtime
    BEFORE UPDATE
    ON t_order
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();
